package com.enfi.api.modules.bz.controller;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.enfi.api.domain.res.ApiResult;
import com.enfi.api.modules.bz.domain.db.ColumnSearchVo;
import com.enfi.api.modules.bz.domain.db.TableSearchVo;
import com.enfi.api.modules.bz.service.DataSourceService;
import com.enfi.api.modules.db.entity.*;
import com.enfi.api.modules.db.service.*;
import com.enfi.api.util.DateUtils;
import com.enfi.api.util.IgnorePropertiesUtil;
import com.enfi.api.util.SecUser;
import com.enfi.api.util.UserUtils;
import com.google.common.collect.Maps;
import org.apache.commons.compress.utils.Lists;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Example;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.persistence.criteria.Predicate;
import javax.servlet.http.HttpServletRequest;
import java.util.*;

/**
 * @author wind
 * @date 2020/3/12
 */
@Controller
@RequestMapping("database")
public class BzDatabaseController {
    @Autowired
    HttpServletRequest request;
    @Autowired
    private DatabaseService databaseService;
    @Autowired
    private DataSourceService dataSourceService;
    @Autowired
    private DatabaseAlreadyConfigTableService databaseAlreadyConfigTableService;
    @Autowired
    private DatabaseBasisInfoService databaseBasisInfoService;
    @Autowired
    private DatabaseDetailNullValueService databaseDetailNullValueService;
    @Autowired
    private DatabaseValueThresholdCheckService databaseValueThresholdCheckService;

    @Autowired
    private JdbcTemplate jdbcTemplate;//数据资产图 数据流图接口数据源
    @Autowired
    private DatabaseStatQualityDataTrendService databaseStatQualityDataTrendService;//数据质量走势
    @Autowired
    private DatabaseStatRulesQuestionService databaseStatRulesQuestionService;//各个规则发现问题数、问题率
    @Autowired
    private DatabaseStatTableQuestionService databaseStatTableQuestionService;//各表发现问题数、问题率
    @Autowired
    private DatabaseStatTrendDetailService databaseStatTrendDetailService;//质量检查明细表

    /**
     * 获取所有数据库列表
     *
     * @return
     */
    @RequestMapping(value = {"getDatabaseList"})
    @ResponseBody
    public Object getDatabaseList() {
        List<Database> databaseList = databaseService.findAll();
        return ApiResult.renderOk().add(databaseList);
    }

    /**
     * 根据数据库ID查询当前数据下所有表
     *
     * @param idDatabase
     * @return
     */
    @RequestMapping(value = {"getTableList"})
    @ResponseBody
    public Object getTableList(String idDatabase) {
        List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();
        Database ds = databaseService.findById(idDatabase).get();
        TableSearchVo tableSearchVo = new TableSearchVo();
        tableSearchVo.setTypes(new String[]{"table"});
        try {
            List list = dataSourceService.getTables(ds, tableSearchVo);
            if (list != null) {
                for (int i = 0; i < list.size(); i++) {
                    Map map = (Map) list.get(i);
                    Map map1 = Maps.newHashMap();
                    map1.put("id", map.get("name"));
                    map1.put("name", map.get("name"));
                    if (StringUtils.equalsIgnoreCase(map.get("type").toString(), "TABLE")) {
                        map1.put("pId", "TABLE");
                    }
                    map1.put("title", map.get("remarks"));
                    mapList.add(map1);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return ApiResult.renderOk().add(mapList);
    }

    /**
     * 根据数据库ID和表名 查询表下所有字段
     *
     * @param idDatabase
     * @param tableName
     * @return
     */
    @RequestMapping(value = {"getColumnList"})
    @ResponseBody
    public Object getColumnList(String idDatabase, String tableName) {
        List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();
        ColumnSearchVo columnSearchVo = new ColumnSearchVo();
        if ("%".equalsIgnoreCase(tableName)) {
            columnSearchVo.setTableNamePattern(tableName);
        }
        columnSearchVo.setId(idDatabase);
        Database ds = databaseService.findById(idDatabase).get();
        try {
            mapList = dataSourceService.getColumns(ds, columnSearchVo);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return ApiResult.renderOk().add(mapList);
    }

    /**
     * 添加已配置规则的表
     *
     * @param databaseAlreadyConfigTable dbListId tableName chTableName
     * @return
     */
    @RequestMapping(value = {"saveDatabaseAlreadyConfigTable"})
    @ResponseBody
    public Object saveDatabaseAlreadyConfigTable(@RequestBody DatabaseAlreadyConfigTable databaseAlreadyConfigTable) {
        SecUser secUser = UserUtils.getSecUser();
        databaseAlreadyConfigTable.setCreateUserId(secUser.getId());
        databaseAlreadyConfigTable.setUpdateUserId(secUser.getId());
        Date now = Calendar.getInstance().getTime();
        databaseAlreadyConfigTable.setCreateTime(now);
        databaseAlreadyConfigTable.setUpdateTime(now);
        databaseAlreadyConfigTableService.save(databaseAlreadyConfigTable);
        return ApiResult.renderOk();
    }

    /**
     * 根据数据库ID查询已配置规则的表的列表
     *
     * @param idDatabase
     * @return
     */
    @RequestMapping(value = {"databaseAlreadyConfigTableList"})
    @ResponseBody
    public Object databaseAlreadyConfigTableList(String idDatabase) {
        List<DatabaseAlreadyConfigTable> databaseAlreadyConfigTableList = Lists.newArrayList();
        if (idDatabase != null) {
            DatabaseAlreadyConfigTable databaseAlreadyConfigTable = new DatabaseAlreadyConfigTable();
            databaseAlreadyConfigTable.setDbListId(idDatabase);
            Sort sort = Sort.by(Sort.Direction.ASC, "createTime");
            databaseAlreadyConfigTableList = databaseAlreadyConfigTableService.findAll(Example.of(databaseAlreadyConfigTable), sort);
        }
        if (idDatabase == null) {
            Sort sort = Sort.by(Sort.Direction.ASC, "createTime");
            databaseAlreadyConfigTableList = databaseAlreadyConfigTableService.findAll(sort);
        }
        return ApiResult.renderOk().add(databaseAlreadyConfigTableList);
    }

    /**
     * 根据ID删除已配置规则的表数据
     *
     * @param id 已配置规则的表主键
     * @return
     */
    @RequestMapping(value = {"deleteDatabaseAlreadyConfigTable"})
    @ResponseBody
    public Object deleteDatabaseAlreadyConfigTable(Integer id) {
        databaseAlreadyConfigTableService.deleteById(id);
        return ApiResult.renderOk();
    }

    /**
     * 根据数据库ID、已配置规则表ID查询规则列表 未级联子表
     *
     * @param alreadyConfigTableId
     * @param dbListId
     * @return
     */
    @RequestMapping(value = {"getDatabaseBasisInfoList"})
    @ResponseBody
    public Object getDatabaseBasisInfoList(String dbListId, Integer alreadyConfigTableId) {
        List<DatabaseBasisInfo> databaseBasisInfoList = new ArrayList<DatabaseBasisInfo>();
        Sort sort = Sort.by(Sort.Direction.ASC, "createTime");
        if (dbListId != null && alreadyConfigTableId != null) {
            DatabaseBasisInfo databaseBasisInfo = new DatabaseBasisInfo();
            databaseBasisInfo.setDbListId(dbListId);
            databaseBasisInfo.setAlreadyConfigTableId(alreadyConfigTableId);
            databaseBasisInfoList = databaseBasisInfoService.findAll(Example.of(databaseBasisInfo), sort);
        }
        if (dbListId != null && alreadyConfigTableId == null) {
            DatabaseBasisInfo databaseBasisInfo = new DatabaseBasisInfo();
            databaseBasisInfo.setDbListId(dbListId);
            databaseBasisInfoList = databaseBasisInfoService.findAll(Example.of(databaseBasisInfo), sort);
        }
        if (dbListId == null && alreadyConfigTableId == null) {
            databaseBasisInfoList = databaseBasisInfoService.findAll(sort);
        }
        return ApiResult.renderOk().add(databaseBasisInfoList);
    }

    /**
     * 添加规则
     *
     * @param databaseBasisInfo
     * @return
     */
    @RequestMapping(value = {"saveDatabaseBasisInfo"})
    @ResponseBody
    public Object saveDatabaseBasisInfo(@RequestBody DatabaseBasisInfo databaseBasisInfo) {
        SecUser secUser = UserUtils.getSecUser();
        databaseBasisInfo.setCreateUserId(secUser.getId());
        databaseBasisInfo.setUpdateUserId(secUser.getId());

        Date now = Calendar.getInstance().getTime();
        databaseBasisInfo.setCreateTime(now);
        databaseBasisInfo.setUpdateTime(now);
        databaseBasisInfoService.save(databaseBasisInfo);//添加主表
        //空值检查添加
        if (databaseBasisInfo.getRulesType() == 2 && databaseBasisInfo.getDatabaseDetailNullValueList() != null && databaseBasisInfo.getDatabaseDetailNullValueList().size() > 0) {
            for (int i = 0; i < databaseBasisInfo.getDatabaseDetailNullValueList().size(); i++) {
                //判断是否添加过，添加过的直接跳过
                DatabaseDetailNullValue databaseDetailNullValue = new DatabaseDetailNullValue();
                databaseDetailNullValue.setDbListId(databaseBasisInfo.getDbListId());
                databaseDetailNullValue.setTableName(databaseBasisInfo.getTableName());
                databaseDetailNullValue.setFieldName(databaseBasisInfo.getDatabaseDetailNullValueList().get(i).getFieldName());
                Optional<DatabaseDetailNullValue> o = databaseDetailNullValueService.findOne(Example.of(databaseDetailNullValue));
                if (!o.isPresent()) {
                    databaseDetailNullValue = new DatabaseDetailNullValue();
                    databaseDetailNullValue.setBasisInfoId(databaseBasisInfo.getId());
                    databaseDetailNullValue.setDbListId(databaseBasisInfo.getDbListId());
                    databaseDetailNullValue.setTableName(databaseBasisInfo.getTableName());
                    databaseDetailNullValue.setFieldName(databaseBasisInfo.getDatabaseDetailNullValueList().get(i).getFieldName());
                    databaseDetailNullValue.setCreateTime(now);
                    databaseDetailNullValue.setUpdateTime(now);
                    databaseDetailNullValueService.save(databaseDetailNullValue);
                }
            }
        }
        //值域检查添加
        if (databaseBasisInfo.getRulesType() == 4 && databaseBasisInfo.getDatabaseValueThresholdCheckList() != null && databaseBasisInfo.getDatabaseValueThresholdCheckList().size() > 0) {
            for (int i = 0; i < databaseBasisInfo.getDatabaseValueThresholdCheckList().size(); i++) {
                //判断是否添加过，添加过的直接跳过
                DatabaseValueThresholdCheck databaseValueThresholdCheck = new DatabaseValueThresholdCheck();
                databaseValueThresholdCheck.setDbListId(databaseBasisInfo.getDbListId());
                databaseValueThresholdCheck.setTableName(databaseBasisInfo.getTableName());
                databaseValueThresholdCheck.setFieldName(databaseBasisInfo.getDatabaseValueThresholdCheckList().get(i).getFieldName());
                Optional<DatabaseValueThresholdCheck> o = databaseValueThresholdCheckService.findOne(Example.of(databaseValueThresholdCheck));
                if (!o.isPresent()) {
                    databaseValueThresholdCheck = new DatabaseValueThresholdCheck();
                    databaseValueThresholdCheck.setBasisInfoId(databaseBasisInfo.getId());
                    databaseValueThresholdCheck.setDbListId(databaseBasisInfo.getDbListId());
                    databaseValueThresholdCheck.setTableName(databaseBasisInfo.getTableName());
                    databaseValueThresholdCheck.setFieldName(databaseBasisInfo.getDatabaseValueThresholdCheckList().get(i).getFieldName());
                    databaseValueThresholdCheck.setLow(databaseBasisInfo.getDatabaseValueThresholdCheckList().get(i).getLow());
                    databaseValueThresholdCheck.setHigh(databaseBasisInfo.getDatabaseValueThresholdCheckList().get(i).getHigh());
                    databaseValueThresholdCheck.setCreateTime(now);
                    databaseValueThresholdCheck.setUpdateTime(now);
                    databaseValueThresholdCheckService.save(databaseValueThresholdCheck);
                }
            }
        }
        return ApiResult.renderOk();
    }

    /**
     * 根据规则ID查看规则详情 级联子表
     *
     * @param id
     * @return
     */
    @RequestMapping(value = {"detailDatabaseBasisInfo"})
    @ResponseBody
    public Object detailDatabaseBasisInfo(Integer id) {
        DatabaseBasisInfo databaseBasisInfo = databaseBasisInfoService.findById(id).get();
        if (databaseBasisInfo != null && databaseBasisInfo.getRulesType() != null) {
            //空值检查
            if (databaseBasisInfo.getRulesType() == 2) {
                DatabaseDetailNullValue databaseDetailNullValueQuery=new DatabaseDetailNullValue();
                databaseDetailNullValueQuery.setBasisInfoId(id);
                Sort sort=Sort.by(Sort.Direction.ASC,"createTime");
                List<DatabaseDetailNullValue> databaseDetailNullValueList = databaseDetailNullValueService.findAll(Example.of(databaseDetailNullValueQuery),sort);
                databaseBasisInfo.setDatabaseDetailNullValueList(databaseDetailNullValueList);
            }
            //值域检查
            if (databaseBasisInfo.getRulesType() == 4) {
                DatabaseValueThresholdCheck databaseValueThresholdCheckQuery=new DatabaseValueThresholdCheck();
                databaseValueThresholdCheckQuery.setBasisInfoId(id);
                Sort sort1=Sort.by(Sort.Direction.ASC,"createTime");
                List<DatabaseValueThresholdCheck> databaseValueThresholdCheckList = databaseValueThresholdCheckService.findAll(Example.of(databaseValueThresholdCheckQuery),sort1);
                databaseBasisInfo.setDatabaseValueThresholdCheckList(databaseValueThresholdCheckList);
            }
        }
        return ApiResult.renderOk().add(databaseBasisInfo);
    }

    /**
     * 根据ID修改规则表状态
     *
     * @param id
     * @param status
     * @return
     */
    @RequestMapping(value = {"updateDatabaseBasisInfoStatus"})
    @ResponseBody
    public Object updateDatabaseBasisInfoStatus(Integer id, Integer status) {
        DatabaseBasisInfo databaseBasisInfo = databaseBasisInfoService.findById(id).get();
        if (databaseBasisInfo != null && Objects.equals(databaseBasisInfo.getStatus(), status)) {
            SecUser secUser = UserUtils.getSecUser();
            databaseBasisInfo.setUpdateUserId(secUser.getId());
            databaseBasisInfo.setUpdateTime(new Date());
            databaseBasisInfo.setStatus(status);
            databaseBasisInfoService.save(databaseBasisInfo);
        }
        return ApiResult.renderOk();
    }

    /**
     * 修改规则 修改前先删除子表，然后添加子表
     *
     * @param databaseBasisInfo
     * @return
     */
    @RequestMapping(value = {"updateDatabaseBasisInfo"})
    @ResponseBody
    public Object updateDatabaseBasisInfo(@RequestBody DatabaseBasisInfo databaseBasisInfo) {
        if (Objects.isNull(databaseBasisInfo.getId())) {
            return ApiResult.renderError("缺少主键");
        }
        Optional<DatabaseBasisInfo> o = databaseBasisInfoService.findById(databaseBasisInfo.getId());
        if (o.isPresent()) {
            DatabaseBasisInfo dbi = o.get();
            BeanUtils.copyProperties(databaseBasisInfo, dbi, IgnorePropertiesUtil.getNullPropertyNames(databaseBasisInfo));
            SecUser secUser=UserUtils.getSecUser();
            dbi.setUpdateUserId(secUser.getId());
            Date now = Calendar.getInstance().getTime();
            dbi.setUpdateTime(now);
            databaseBasisInfoService.save(dbi);
            if (dbi.getRulesType() == 2) {
                DatabaseDetailNullValue databaseDetailNullValueQuery=new DatabaseDetailNullValue();
                databaseDetailNullValueQuery.setBasisInfoId(dbi.getId());
                databaseDetailNullValueService.deleteAll(databaseDetailNullValueService.findAll(Example.of(databaseDetailNullValueQuery)));
                if (databaseBasisInfo.getDatabaseDetailNullValueList() != null) {
                    for (int i = 0; i < databaseBasisInfo.getDatabaseDetailNullValueList().size(); i++) {
                        DatabaseDetailNullValue databaseDetailNullValueQuery1=new DatabaseDetailNullValue();
                        databaseDetailNullValueQuery1.setDbListId(databaseBasisInfo.getDbListId());
                        databaseDetailNullValueQuery1.setTableName( databaseBasisInfo.getTableName());
                        databaseDetailNullValueQuery1.setFieldName(databaseBasisInfo.getDatabaseDetailNullValueList().get(i).getFieldName());
                        //判断是否添加过，添加过的直接跳过
                        Optional<DatabaseDetailNullValue> t = databaseDetailNullValueService.findOne(Example.of(databaseDetailNullValueQuery1));
                        if (!t.isPresent()) {
                            DatabaseDetailNullValue databaseDetailNullValue = new DatabaseDetailNullValue();
                            databaseDetailNullValue.setBasisInfoId(databaseBasisInfo.getId());
                            databaseDetailNullValue.setDbListId(databaseBasisInfo.getDbListId());
                            databaseDetailNullValue.setTableName(databaseBasisInfo.getTableName());
                            databaseDetailNullValue.setFieldName(databaseBasisInfo.getDatabaseDetailNullValueList().get(i).getFieldName());
                            databaseDetailNullValue.setCreateTime(now);
                            databaseDetailNullValue.setUpdateTime(now);
                            databaseDetailNullValueService.save(databaseDetailNullValue);
                        }
                    }
                }
            }
            if (dbi.getRulesType() == 4) {
                DatabaseValueThresholdCheck databaseValueThresholdCheckQuery=new DatabaseValueThresholdCheck();
                databaseValueThresholdCheckQuery.setBasisInfoId(dbi.getId());
                databaseValueThresholdCheckService.deleteAll(databaseValueThresholdCheckService.findAll(Example.of(databaseValueThresholdCheckQuery)));
                if (databaseBasisInfo.getDatabaseValueThresholdCheckList() != null) {
                    for (int i = 0; i < databaseBasisInfo.getDatabaseValueThresholdCheckList().size(); i++) {
                        //判断是否添加过，添加过的直接跳过
                        DatabaseValueThresholdCheck databaseValueThresholdCheckQuery1=new DatabaseValueThresholdCheck();
                        databaseValueThresholdCheckQuery1.setDbListId(databaseBasisInfo.getDbListId());
                        databaseValueThresholdCheckQuery1.setTableName( databaseBasisInfo.getTableName());
                        databaseValueThresholdCheckQuery1.setFieldName(databaseBasisInfo.getDatabaseValueThresholdCheckList().get(i).getFieldName());
                        Optional<DatabaseValueThresholdCheck> p= databaseValueThresholdCheckService.findOne(Example.of(databaseValueThresholdCheckQuery1));
                        if (!p.isPresent()) {
                            DatabaseValueThresholdCheck databaseValueThresholdCheck = new DatabaseValueThresholdCheck();
                            databaseValueThresholdCheck.setBasisInfoId(databaseBasisInfo.getId());
                            databaseValueThresholdCheck.setDbListId(databaseBasisInfo.getDbListId());
                            databaseValueThresholdCheck.setTableName(databaseBasisInfo.getTableName());
                            databaseValueThresholdCheck.setFieldName(databaseBasisInfo.getDatabaseValueThresholdCheckList().get(i).getFieldName());
                            databaseValueThresholdCheck.setLow(databaseBasisInfo.getDatabaseValueThresholdCheckList().get(i).getLow());
                            databaseValueThresholdCheck.setHigh(databaseBasisInfo.getDatabaseValueThresholdCheckList().get(i).getHigh());
                            databaseValueThresholdCheck.setCreateTime(now);
                            databaseValueThresholdCheck.setUpdateTime(now);
                            databaseValueThresholdCheckService.save(databaseValueThresholdCheck);
                        }
                    }
                }
            }
        }
        return ApiResult.renderOk();
    }

    /**
     * 根据ID删除规则 需删除子表
     *
     * @param id
     * @param rulesType 规则类型
     * @return
     */
    @RequestMapping(value = {"deleteDatabaseBasisInfo"})
    @ResponseBody
    public Object deleteDatabaseBasisInfo(Integer id, Integer rulesType) {
        databaseBasisInfoService.deleteById(id);
        if (rulesType == 2) {
            DatabaseDetailNullValue databaseDetailNullValueQuery=new DatabaseDetailNullValue();
            databaseDetailNullValueQuery.setBasisInfoId(id);
            databaseDetailNullValueService.deleteAll(databaseDetailNullValueService.findAll(Example.of(databaseDetailNullValueQuery)));
        }
        if (rulesType == 4) {
            DatabaseValueThresholdCheck databaseValueThresholdCheckQuery=new DatabaseValueThresholdCheck();
            databaseValueThresholdCheckQuery.setBasisInfoId(id);
            databaseValueThresholdCheckService.deleteAll(databaseValueThresholdCheckService.findAll(Example.of(databaseValueThresholdCheckQuery)));
        }
        return ApiResult.renderOk();
    }

    /**
     * 质量检查报表 5个
     *
     * @return
     */
    @RequestMapping(value = {"getQualityCheckStat"})
    @ResponseBody
    public Object getQualityCheckStat() {
        Map<String, Object> map = new HashMap<>();
        Sort sort = Sort.by(Sort.Order.asc("date"));
        List<DatabaseStatQualityDataTrend> databaseStatQualityDataTrendList = databaseStatQualityDataTrendService.findAll(sort);
        map.put("databaseStatQualityDataTrendList", databaseStatQualityDataTrendList);//数据质量走势

        Date date = DateUtils.getFormatDate(DateUtils.addDay(new Date(), 0), DateUtils.DATE_FORMAT_NOTIME);//当前日期的前一天才有数据
        DatabaseStatRulesQuestion databaseStatRulesQuestionQuery=new DatabaseStatRulesQuestion();
        databaseStatRulesQuestionQuery.setDate(date);
        Sort sort1=Sort.by(Sort.Direction.DESC,"questionPercent");
        List<DatabaseStatRulesQuestion> databaseStatRulesQuestionList = databaseStatRulesQuestionService.findAll(Example.of(databaseStatRulesQuestionQuery),sort1);
        map.put("databaseStatRulesQuestionList", databaseStatRulesQuestionList);//各个规则发现问题数、问题率

        DatabaseStatTableQuestion databaseStatTableQuestionQuery=new DatabaseStatTableQuestion();
        databaseStatTableQuestionQuery.setDate(date);
        Sort sort2=Sort.by(Sort.Direction.DESC,"questionPercent");
        List<DatabaseStatTableQuestion> databaseStatTableQuestionList = databaseStatTableQuestionService.findAll(Example.of(databaseStatTableQuestionQuery),sort2);
        map.put("databaseStatTableQuestionList", databaseStatTableQuestionList);//各表发现问题数、问题率
        return ApiResult.renderOk().add(map);
    }

    /**
     * 质量检查明细表 分页
     *
     * @param databaseStatTrendDetail dbListId rulesType tableName status date
     * @return
     */
    @RequestMapping(value = {"getStatTrendDetailList"})
    @ResponseBody
    public Object getStatTrendDetailList(DatabaseStatTrendDetail databaseStatTrendDetail, Pageable pageable) {
        Page<DatabaseStatTrendDetail> list = databaseStatTrendDetailService.findAll((root, query, builder) -> {
            List<Predicate> predicates = new ArrayList<>();
            if (StringUtils.isNotBlank(databaseStatTrendDetail.getDbListId())) {
                predicates.add(builder.equal(root.get("dbListId"), databaseStatTrendDetail.getDbListId()));
            }
            if (!Objects.isNull(databaseStatTrendDetail.getRulesType())) {
                predicates.add(builder.equal(root.get("rulesType"), databaseStatTrendDetail.getRulesType()));
            }
            if (StringUtils.isNotBlank(databaseStatTrendDetail.getTableName())) {
                predicates.add(builder.equal(root.get("tableName"), databaseStatTrendDetail.getTableName()));
            }
            if (!Objects.isNull(databaseStatTrendDetail.getStatus())) {
                predicates.add(builder.equal(root.get("status"), databaseStatTrendDetail.getStatus()));
            }
            if (!Objects.isNull(databaseStatTrendDetail.getDate())) {
                predicates.add(builder.equal(root.get("date"), databaseStatTrendDetail.getDate()));
            }
            query.where(predicates.toArray(new Predicate[predicates.size()]));
            return null;
        }, pageable);
        return ApiResult.renderOk().add(list);
    }


    /**
     * 获取数据资产视图数据
     *
     * @return
     */
    @RequestMapping(value = {"getDataAssetsData"})
    @ResponseBody
    @DS("slave_1")
    public Object getDataAssetsData() {
        Map<String, Object> map = new HashMap<String, Object>();
        String sql1 = "SELECT DATE_FORMAT(jobdate, '%Y%m%d') as date" +
                ", round((COUNT(IF(job_state LIKE '%success%', 1, NULL)) / COUNT(job_state))*100,2) AS percent" +
                " FROM job_log" +
                " GROUP BY DATE_FORMAT(jobdate, '%Y%m%d')";
        List<Map<String, Object>> taskRunAllTrendList = jdbcTemplate.queryForList(sql1);

        String sql2 = "SELECT date," +
                "round((failed_job/total_job)*100,2) failed," +
                "round((succeeded_job/total_job)*100,2) succeeded," +
                "round((running_job/total_job)*100,2) running" +
                " FROM (" +
                "SELECT DATE_FORMAT(jobdate,'%Y%m%d') date" +
                ", COUNT(*) total_job" +
                ", COUNT(if(job_state LIKE '%error%', 1, NULL)) failed_job" +
                ", COUNT(if(job_state LIKE '%success%', 1, NULL)) succeeded_job" +
                ", COUNT(if(job_state LIKE '%running%', 1, NULL)) running_job" +
                " FROM job_log" +
                " GROUP BY DATE_FORMAT(jobdate,'%Y%m%d') desc" +
                ") tmp";
        List<Map<String, Object>> taskMonitorStatList = jdbcTemplate.queryForList(sql2);

        String sql3 = "SELECT DATE_FORMAT(jobdate,'%Y%m%d') date" +
                ", COUNT(*) total" +
                ", COUNT(if(job_state LIKE '%error%', 1, NULL)) failed" +
                ", COUNT(if(job_state LIKE '%success%', 1, NULL)) succeeded" +
                ", COUNT(if(job_state LIKE '%running%', 1, NULL)) running" +
                " FROM job_log" +
                " GROUP BY DATE_FORMAT(jobdate,'%Y%m%d') desc";
        List<Map<String, Object>> taskSituationList = jdbcTemplate.queryForList(sql3);

        String sql4 = "SELECT CASE" +
                "    WHEN job_state LIKE '%sqoop_in%' THEN CONCAT(table_name, ' 数据导入')" +
                "    WHEN job_state LIKE '%hive_etl%' THEN CONCAT(table_name, ' 数据清洗')" +
                "    WHEN job_state LIKE '%sqoop_out%' THEN CONCAT(table_name, ' 数据导出')" +
                "    ELSE CONCAT(table_name, ' truncate')" +
                "  END AS job_name" +
                "  , cost_time" +
                " FROM job_log" +
                " WHERE DATE_FORMAT(jobdate, 'Y%m%d%') = DATE_FORMAT(NOW(), 'Y%m%d%')" +
                " ORDER BY  cast(`cost_time` as signed integer)  desc limit 0,10";
        List<Map<String, Object>> taskRunRankList = jdbcTemplate.queryForList(sql4);

        String sql5 = "SELECT count(if(job_level='Ⅰ级',1,NULL)) l_count" +
                "      ,count(if(job_level='Ⅱ级',1,NULL)) m_count" +
                "      ,count(if(job_level='Ⅲ级',1,NULL)) h_count" +
                " FROM (" +
                "  SELECT CASE" +
                "        WHEN job_state LIKE '%sqoop_in%' THEN 'Ⅰ级'" +
                "        WHEN job_state LIKE '%hive_etl%' THEN 'Ⅱ级'" +
                "        WHEN job_state LIKE '%sqoop_out%' THEN 'Ⅲ级'" +
                "        ELSE 'Ⅲ级'" +
                "      END AS job_level, cost_time" +
                "    FROM job_log" +
                "    WHERE DATE_FORMAT(jobdate, 'Y%m%d%') = DATE_FORMAT(NOW(), 'Y%m%d%')" +
                ") tmp";
        Map<String, Object> dataPriorityStat = jdbcTemplate.queryForMap(sql5);

        String sql6 = "SELECT COUNT(*) total_job FROM job_log";
        Map<String, Object> taskTotalCount = jdbcTemplate.queryForMap(sql6);

        String sql7 = "SELECT tmp1.total_job" +
                "      ,tmp1.succeeded_job" +
                "      ,if(tmp1.total_job-tmp2.total_job<0,0,tmp1.total_job-tmp2.total_job) new_job" +
                " from (" +
                "SELECT COUNT(*) total_job" +
                "  , COUNT(if(job_state LIKE '%success%', 1, NULL)) succeeded_job" +
                " FROM job_log" +
                " WHERE DATE_FORMAT(jobdate,'%Y%m%d')=DATE_FORMAT(NOW(),'%Y%m%d')) tmp1" +
                " LEFT JOIN" +
                "(SELECT COUNT(*) total_job" +
                " FROM job_log" +
                " WHERE DATE_FORMAT(jobdate,'%Y%m%d')=DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -1 day),'%Y%m%d')) tmp2" +
                " ON TRUE";
        Map<String, Object> taskCountStat = jdbcTemplate.queryForMap(sql7);

        map.put("taskRunAllTrendList", taskRunAllTrendList);//任务运行总体趋势
        map.put("taskMonitorStatList", taskMonitorStatList);//ETL任务监控统计
        map.put("taskSituationList", taskSituationList);//任务概况
        map.put("taskRunRankList", taskRunRankList);//任务运行排行
        map.put("dataPriorityStat", dataPriorityStat);//数据优先级
        map.put("taskTotalCount", taskTotalCount);//任务总数
        map.put("taskCountStat", taskCountStat);//运行、调用、完成、新增任务数
        return ApiResult.renderOk().add(map);
    }

    /**
     * 获取数据资产流图数据
     *
     * @return
     */
    @RequestMapping(value = {"getDataAssetsFlowData"})
    @ResponseBody
    @DS("datastat")
    public Object getDataAssetsFlowData() {
        Map<String, Object> map = new HashMap<String, Object>();
        String sql1 = "SELECT DATE_FORMAT(lastupdate, '%Y%m%d') date" +
                ", CONCAT(ROUND(SUM(size), 3), 'M') size" +
                ", CONCAT(ROUND(SUM(total_size), 3), 'M') total_size" +
                " FROM (" +
                " SELECT stage, data_base, fields_num, counts_num, table_num" +
                ", lastupdate" +
                ", CASE" +
                " WHEN size LIKE '%M%' THEN SUBSTRING_INDEX(size, 'M', 1)" +
                " WHEN size LIKE '%G%' THEN SUBSTRING_INDEX(size, 'G', 1) * 1000" +
                " END AS size" +
                ", CASE" +
                " WHEN size LIKE '%M%' THEN SUBSTRING_INDEX(total_size, 'M', 1)" +
                " WHEN size LIKE '%G%' THEN SUBSTRING_INDEX(total_size, 'G', 1) * 1000" +
                " END AS total_size" +
                " FROM data_assets" +
                " WHERE stage=1" +
                ") tmp" +
                " GROUP BY DATE_FORMAT(lastupdate, '%Y%m%d') desc";
        List<Map<String, Object>> dataGovernTotalList = jdbcTemplate.queryForList(sql1);

        String sql2 = "SELECT DATE_FORMAT(lastupdate, '%Y%m%d') date" +
                ", CONCAT(ROUND(SUM(size), 3), 'M') size" +
                ", CONCAT(ROUND(SUM(total_size), 3), 'M') total_size" +
                " FROM (" +
                " SELECT stage, data_base, fields_num, counts_num, table_num" +
                ", lastupdate" +
                ", CASE" +
                " WHEN size LIKE '%M%' THEN SUBSTRING_INDEX(size, 'M', 1)" +
                " WHEN size LIKE '%G%' THEN SUBSTRING_INDEX(size, 'G', 1) * 1000" +
                " END AS size" +
                " , CASE" +
                " WHEN size LIKE '%M%' THEN SUBSTRING_INDEX(total_size, 'M', 1)" +
                " WHEN size LIKE '%G%' THEN SUBSTRING_INDEX(total_size, 'G', 1) * 1000" +
                " END AS total_size" +
                " FROM data_assets" +
                " WHERE stage=3" +
                ") tmp" +
                " GROUP BY DATE_FORMAT(lastupdate, '%Y%m%d') desc";
        List<Map<String, Object>> cleanDataTotalList = jdbcTemplate.queryForList(sql2);

        String sql3 = "SELECT tmp1.table_num" +
                ",tmp1.fields_num" +
                ",tmp1.counts_num" +
                ",tmp1.size" +
                ",tmp1.total_size" +
                ",tmp2.success_job" +
                ",tmp2.run_job" +
                ",tmp2.total_job" +
                ",tmp2.job_state" +
                ",tmp2.total_job-tmp2.success_job as fail_job" +
                " FROM (" +
                " SELECT SUM( table_num) AS table_num" +
                ", SUM(fields_num) AS fields_num" +
                ", SUM(counts_num) AS counts_num" +
                ", CONCAT(ROUND(SUM(size), 3), 'G') AS size" +
                ", CONCAT(ROUND(SUM(total_size), 3), 'G') AS total_size" +
                " FROM (" +
                " SELECT stage, data_base, fields_num, counts_num, table_num" +
                ", lastupdate" +
                ", CASE" +
                " WHEN size LIKE '%M%' THEN SUBSTRING_INDEX(size, 'M', 1) / 1000" +
                " WHEN size LIKE '%G%' THEN SUBSTRING_INDEX(size, 'G', 1)" +
                " END AS size" +
                ", CASE" +
                " WHEN size LIKE '%M%' THEN SUBSTRING_INDEX(total_size, 'M', 1) / 1000" +
                " WHEN size LIKE '%G%' THEN SUBSTRING_INDEX(total_size, 'G', 1)" +
                " END AS total_size" +
                " FROM data_assets" +
                " WHERE stage=1" +
                ") tmp" +
                " WHERE DATE_FORMAT(lastupdate, '%Y%m%d') = DATE_FORMAT(NOW(), '%Y%m%d')" +
                ") tmp1" +
                " JOIN (" +
                " SELECT COUNT(1) total_job" +
                "  ,COUNT(if(job_state LIKE '%running%', 1, NULL)) run_job" +
                ",COUNT(if(job_state LIKE '%success%', 1, NULL)) success_job" +
                ",(COUNT(if(job_state LIKE '%success%', 1, NULL)) / COUNT(1))*100 AS job_state" +
                " FROM job_log" +
                " WHERE DATE_FORMAT(jobdate, '%Y%m%d') = DATE_FORMAT(NOW(), '%Y%m%d')" +
                " AND job_state LIKE '%hive_etl%'" +
                ") tmp2" +
                " ON true";
        Map<String, Object> countStat = jdbcTemplate.queryForMap(sql3);

        Sort sort = Sort.by(Sort.Order.desc("date"));
        List<DatabaseStatQualityDataTrend> databaseStatQualityDataTrendList = databaseStatQualityDataTrendService.findAll(sort);

        map.put("dataGovernTotalList", dataGovernTotalList);//数据治理总量 （单位M）
        map.put("cleanDataTotalList", cleanDataTotalList);//数据清洗后总量 （单位M）
        map.put("countStat", countStat);//数量统计
        map.put("databaseStatQualityDataTrendList", databaseStatQualityDataTrendList);//问题数
        return ApiResult.renderOk().add(map);
    }
}
